<?php
/**
 * Database functions go in here
 */

/**
 *  Saves a grid test to the database and returns a unique test ID
 */
function gtSaveGridTest( array $questions, $name ) {
    $serialized = serialize( $questions );
    $hash = md5( $serialized );

    $db = wfGetDB( DB_SLAVE );
    $res = $db->selectRow(
        'test',
        array( 'id' ),
        "hash = '$hash'"
    );
    if( $res ) {
        return $res->id;
    } else {
        $db->begin();
        $db->insert(
            'test',
            array(
                'name' => $name,
                'serialized' => $serialized,
                'hash' => $hash
            )
        );
        $id = $db->insertId();
        $db->commit();
        return $id;
    }
}

function gtSaveSubmittedTest( $post ) {
    global $wgUser;

    $db = wfGetDB( DB_MASTER );

    // gets the test data for the submitted test
    $test = $db->selectRow(
        'test',
        array( 'serialized' ),
        "id = '{$post['test_id']}'"
    );

    // submit the test and get its id for individual anwer insertion
    $db->begin();
    $db->insert(
        'submitted_test',
        array(
            'user_id' => $wgUser->getId(),
            'grid_id' => $post['test_id'],
            'sent' => time(),
        )
    );
    $test_id = $db->insertId();
    $db->commit();

    $questions = unserialize( $test->serialized );

    // submit each answer
    foreach( $questions as $key => $question ) {
        $answers = $post[md5( $question['question'] )];
        if( !is_array( $answers ) ) {
            // treat individual answers as arrays, to pass
            // them trhough the foreach
            $answers = array( $answers );
        }
        foreach( $answers as $answer ) {
            $db->insert(
                'submitted_answer',
                array(
                    'submitted_test_id' => $test_id,
                    'question_id' => $key,
                    'value' => $answer
                )
            );
        }
    }
}


function gtGetTest( $id ) {
    $db = wfGetDB( DB_SLAVE ); // we don't need the master for a select

    $sql = "
        SELECT u.user_id, u.user_name, t.id AS test_id, t.sent, bt.serialized
        FROM mw_user AS u
        INNER JOIN mw_submitted_test AS t
            ON t.user_id = u.user_id
        JOIN mw_test AS bt
            ON bt.id = t.grid_id
        WHERE t.id = ?
    ";
    $res = $db->safeQuery( $sql, $id );
    $data = $res->fetchRow();

    $data['questions'] = unserialize( $data['serialized'] );
    unset( $data['serialized'] );


    $sql = "
        SELECT *
        FROM mw_submitted_answer
        WHERE submitted_test_id = ?    
    ";
    $res = $db->safeQuery( $sql, $id );
    while( $row = $res->fetchRow() ) {
        $data['answers'][] = $row;
    }

    return $data;
}


function gtGetUsersWithActiveTests() {
    $db = wfGetDB( DB_SLAVE );

    $sql = "
        SELECT u.user_id, u.user_name, t.id AS test_id, t.sent
        FROM mw_user AS u
        INNER JOIN mw_submitted_test AS t
            ON t.user_id = u.user_id
     "; // the join ensures that we select just users with active tests
    
    $res = $db->query( $sql );

    $data = array();
    while( $row = $res->fetchRow() ) {
        $data[] = $row;
    }

    return $data;
}
